const sql = require('mssql');
const baseFn = require('../../public/base.js');

/**
 * 
 * 订单状态查询
 */
/**
 * 
 * 获得订单信息
 */
const getOrderStatusInfor = async (ctx, next) => {
    const body = ctx.request.body;
    let totalSql = `SELECT  COUNT(Z.ID) AS TOTAL FROM XSD_ZB Z LEFT JOIN (SELECT id,carId FROM YHDD GROUP BY id,carId) T1 ON Z.ID = T1.id WHERE Z.RQ > = '${baseFn.getDayAgo(30)}' `;
    let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Z.ID) AS px,Z.RQ,Z.ID,Z.DWMC,Z.LXDH,T1.MC,T1.carId,CASE WHEN F.ID IS NULL 
        THEN '未接单' WHEN F.ID IS NOT NULL AND T1.id IS NULL THEN '未释放' ELSE '已接单' END AS STATUS 
        FROM XSD_ZB Z LEFT JOIN FPDD F ON F.ID = Z.ID LEFT JOIN 
        (SELECT STUFF((SELECT ','+B.MC FROM YHDD H LEFT JOIN BM_YG B ON H.userId = B.BM 
        WHERE Y.id = H.id GROUP BY B.MC   FOR XML PATH('')),1,1,'') as MC,id,carId FROM YHDD Y GROUP BY id,carId) 
        T1 ON Z.ID = T1.id WHERE Z.RQ > = '${baseFn.getDayAgo(30)}' `;
    if (body.searchValList.carid) {
        startSql += ` AND T1.carId = '${body.searchValList.carid}'`;
        totalSql += ` AND T1.carId = '${body.searchValList.carid}'`;
    }
    if (body.searchValList.id) {
        startSql += ` AND (Z.ID = '${body.searchValList.id}' OR Z.ID_DHD = '${body.searchValList.id}')`;
        totalSql += ` AND (Z.ID = '${body.searchValList.id}' OR Z.ID_DHD = '${body.searchValList.id}')`;
    }
    if (body.searchValList.customer) {
        startSql += `AND (Z.DWMC = '${body.searchValList.customer}' OR Z.LXDH = '${body.searchValList.customer}')`;
        totalSql += ` AND (Z.DWMC = '${body.searchValList.customer}' OR Z.LXDH = '${body.searchValList.customer}')`;
    }
    if (body.searchValList.status) {
       
        // 查询未接单
        if (body.searchValList.status == 1) {
            startSql += ` AND Z.ID <>ALL (SELECT ID FROM FPDD)`;

            totalSql += ` AND Z.ID <>ALL (SELECT ID FROM FPDD)`;
        } else if (body.searchValList.status == 2) {
            // 查询未释放
            startSql += ` AND Z.ID IN (SELECT ID FROM FPDD ) AND Z.ID <>ALL (SELECT id FROM YHDD)`;

            totalSql += ` AND Z.ID IN (SELECT ID FROM FPDD ) AND Z.ID <>ALL (SELECT id FROM YHDD)`;
        } else if (body.searchValList.status == 3) {
            // 查询已接单
            startSql += ` AND Z.ID IN (SELECT ID FROM FPDD ) AND Z.ID IN (SELECT id FROM YHDD)`;

            totalSql += ` AND Z.ID IN (SELECT ID FROM FPDD ) AND Z.ID IN (SELECT id FROM YHDD)`;
        }


    }
    if (body.searchValList.yptm) {
        startSql += ` AND Z.id IN (SELECT V.ID FROM XSD_MX V LEFT JOIN BM_YP Q ON V.YPBM =  Q.BM   WHERE (Q.TM LIKE '%${body.searchValList.yptm}%' OR Q.JP LIKE '%${body.searchValList.yptm}%' OR Q.MC LIKE '%${body.searchValList.yptm}%'))`;
        totalSql += ` AND Z.id IN (SELECT V.ID FROM XSD_MX V LEFT JOIN BM_YP Q ON V.YPBM =  Q.BM   WHERE (Q.TM LIKE '%${body.searchValList.yptm}%' OR Q.JP LIKE '%${body.searchValList.yptm}%' OR Q.MC LIKE '%${body.searchValList.yptm}%'))`;
    }
    startSql += `) AS t2 WHERE t2.px between ${(body.pageNum - 1) * 5 + 1} and ${body.pageNum * 5} `
    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);
    ctx.body = { total: total.recordset[0].TOTAL, result: result.recordset }
}
/**
 * 选择拣货订单展示对应订单下商品的信息
 */


const getOrderStatusShopInfor = async (ctx, next) => {
    const body = ctx.request.body;
    let startSql = `SELECT M.YPBM,M.YPMC,M.YPGG,M.JLDW,M.SCCJ,M.SL,M.SCPH,M.YXQZ,Y.JHZT,Y.FHZT,Y.SFYC,Y.JHRQ,Y.FHRQ,Y.JHZL  FROM XSD_MX M LEFT JOIN YHDD Y ON M.ID = Y.id AND M.YPBM = Y.YPBM AND M.SCPH = Y.SCPH WHERE M.ID = '${body.chooseVal}' `;
    let result = await sql.query(startSql);
    ctx.body = { result: result.recordset }
}
/**
 * 释放订单
 */


const releaseOrder = async (ctx, next) => {
    const body = ctx.request.body;
    let result = await sql.query(`DELETE FROM FPDD WHERE ID = '${body.id}'`);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }
}

/**
 * 复核订单
 */


const reviewOrder = async (ctx, next) => {
    const body = ctx.request.body;
    const shopList = body.shopList;
    const id = body.id;
    const userId = body.userId;
    if (0 in shopList) {
        let insertFpddResult = await sql.query(`INSERT INTO FPDD VALUES('${id}')`);
        let insertYhddSql = `INSERT INTO YHDD(userid, id, carid, YPBM, JHZT, SCPH,FHZT, SFYC, JHRQ, FHRQ) `;
        shopList.forEach((item, ind) => {
            insertYhddSql += ` SELECT ${userId}, '${id}', '', '${item.YPBM}', 'T', '${item.SCPH}', 'T', 'T', '${baseFn.getNowTimeId().fullDate}', '${baseFn.getNowTimeId().fullDate}' `;
            if (ind !== shopList.length - 1) {
                insertYhddSql += ' UNION ';
            }
        });
        let insertYhddResult = await sql.query(insertYhddSql);
        if (insertYhddResult.rowsAffected[0] > 0 && insertFpddResult.rowsAffected[0] > 0) {
            ctx.body = { success: true }
        } else {
            ctx.body = { success: false }
        }
    } else {
        ctx.body = { success: false }
    }
}





module.exports = {
    'POST /getOrderStatusInfor': getOrderStatusInfor,
    'POST /getOrderStatusShopInfor': getOrderStatusShopInfor,
    'POST /releaseOrder': releaseOrder,
    'POST /reviewOrder': reviewOrder
}